package dao;

import entity.User;
import util.DBTools;

import java.sql.*;
import java.util.Date;


public class UserDao {

    //添加user记录
    public int addUser(String username, String password, String nickname){
        //返回操作记录数，插入条数
        int total = 0;
        String sql = "insert into user(username,password,nickname,avatar,addtime) values(?,?,?,?,?)";
        //加载驱动，创建连接，执行sql，释放资源
        Connection connection = DBTools.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            ps.setString(3, nickname);
            ps.setString(4, "https://img1.baidu.com/it/u=592570905,1313515675&fm=253&fmt=auto&app=138&f=JPEG?w=500&h=500");
            //设置当前系统时间
            //设置当前系统的时间
            Date now = new Date();
            Timestamp addtime = new Timestamp(now.getTime());
            ps.setTimestamp(5, addtime);
            //将执行的结果返回total变量
            total = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBTools.close(connection, ps, null);
        }


        return total;
    }

    public User selectUserByname(String username) {

        //根据用户名查询用户记录，并将用户记录抽象成user对象
        User user = null;

        String sql = "select * from user where username = ?";
        Connection connection = DBTools.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            while (rs.next()) {

                //获取到记录每一个列值
                Integer id = rs.getInt("id");
                String uname = rs.getString("username");
                String password = rs.getString("password");
                String nickname = rs.getString("nickname");
                String avatar = rs.getString("avatar");
                Integer status = rs.getInt("status");
                Timestamp addtime = rs.getTimestamp("addtime");
                Date time = new Date(addtime.getTime());

                //将列值转化为对象的属性值
                user = new User();
                user.setId(id);
                user.setUsername(uname);
                user.setNickname(nickname);
                user.setPassword(password);
                user.setStatus(status);
                user.setAvatar(avatar);
                user.setAddtime(time);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBTools.close(connection, ps, rs);
        }
        return user;
    }

    public static int getUserTotal(){
        int total = 0;
        String sql = "select count(*) from user where status = 0;";
        Connection connection = DBTools.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                total = rs.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBTools.close(connection,ps,rs);
        }
        return total;
    }
}

